from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, Session, scoped_session
import logging
from typing import Generator
from contextlib import contextmanager
from app.core.config import settings

logger = logging.getLogger(__name__)

# 使用db_config属性访问数据库配置
db_config = settings.db_config

# 构建数据库连接URL（避免在日志中暴露密码）
_db_url = f"mysql+pymysql://{db_config.user}:{'*' * len(db_config.password)}@{db_config.host}:{db_config.port}/{db_config.database}"
logger.info(f"Initializing database connection to {_db_url}")

# 创建数据库引擎
engine = create_engine(
    f"mysql+pymysql://{db_config.user}:{db_config.password}@{db_config.host}:{db_config.port}/{db_config.database}",
    pool_size=db_config.pool.size,
    max_overflow=db_config.pool.max_overflow,
    pool_timeout=db_config.pool.timeout,
    pool_pre_ping=True,
    pool_recycle=db_config.pool.recycle,
    echo=False,
    pool_use_lifo=True,
    execution_options={
        'isolation_level': 'READ COMMITTED'
    }
)
logger.info(f"Database engine created with pool_size={db_config.pool.size}, max_overflow={db_config.pool.max_overflow}, pool_recycle={db_config.pool.recycle}")

# 创建线程本地会话工厂
SessionLocal = scoped_session(
    sessionmaker(autocommit=False, autoflush=False, bind=engine)
)

# 创建基类
Base = declarative_base()

# 数据库连接测试
def test_db_connection() -> bool:
    """测试数据库连接是否正常"""
    try:
        with engine.connect() as conn:
            conn.execute("SELECT 1")
        logger.info("Database connection test successful")
        return True
    except Exception as e:
        logger.error(f"Database connection test failed: {e}")
        return False

# 依赖项，用于获取数据库会话
def get_db() -> Generator[Session, None, None]:
    """获取数据库会话的依赖项"""
    db: Session = SessionLocal()
    try:
        yield db
        # 确保事务成功提交
        db.commit()
    except Exception:
        # 出现异常时回滚事务
        db.rollback()
        raise
    finally:
        # 确保会话关闭
        db.close()
        # 移除线程本地存储中的会话
        SessionLocal.remove()

# 批量操作辅助函数
def batch_operation(db: Session, operations: list, batch_size: int = 1000) -> None:
    """批量执行数据库操作
    
    Args:
        db: 数据库会话
        operations: 要执行的操作列表
        batch_size: 每批处理的记录数
    """
    try:
        for i in range(0, len(operations), batch_size):
            batch = operations[i:i + batch_size]
            db.add_all(batch)
            db.commit()
            logger.debug(f"Processed batch of {len(batch)} records")
    except Exception as e:
        db.rollback()
        logger.error(f"Batch operation failed: {e}")
        raise